/*
  asi_SeasonalAddress

  Updates AddressToUse.FullAddressKey values
  based on the SeasonalFullAddress table.
*/

CREATE  PROCEDURE [dbo].[asi_SeasonalAddress] AS

/* Variables used by Procedure */

DECLARE @Now DATETIME
SET @Now = GETDATE()
DECLARE @NotSeasonalInstances INT

/* Select all SeasonalFullAddress -> curSeasonalFullAddress */
DECLARE curSeasonalFullAddress CURSOR FOR
  SELECT FullAddressKey,
     ReplacesFullAddressKey,
     BeginDate,
     EndDate,
     IsSeasonalAnnually
  FROM      SeasonalFullAddress
  /*
    A non-annual address will override an annual address in the same timeframe.
    Ordering by IsSeasonalAnnually DESC will let non-annual addresses do this.
    Ordering by BeginDate ASC allows one seasonal address to transparently follow another.
  */
  ORDER BY IsSeasonalAnnually DESC, BeginDate ASC

/* Variables used by a SeasonalFullAddress */
DECLARE @FullAddressKey UNIQUEIDENTIFIER
DECLARE @ReplacesFullAddressKey UNIQUEIDENTIFIER
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @IsSeasonalAnnually BIT

/* Open: curSeasonalFullAddress */
OPEN curSeasonalFullAddress

FETCH NEXT
  FROM curSeasonalFullAddress
  INTO  @FullAddressKey,
    @ReplacesFullAddressKey,
    @BeginDate,
    @EndDate,
    @IsSeasonalAnnually

/* Iterate: curSeasonalFullAddress */
WHILE @@FETCH_STATUS = 0
BEGIN

  /* If it's seasonal annually, bring the year up to this year */
  IF (@IsSeasonalAnnually = 1)
  BEGIN
    SET @BeginDate = CAST(CAST(DATEPART(mm, @BeginDate) AS NVARCHAR(2)) + '/' + CAST(DATEPART(dd, @BeginDate) AS NVARCHAR(2)) + '/' + CAST(DATEPART(yyyy, @Now) AS NVARCHAR(4)) AS DATETIME)
    SET @EndDate = CAST(CAST(DATEPART(mm, @EndDate) AS NVARCHAR(2)) + '/' + CAST(DATEPART(dd, @EndDate) AS NVARCHAR(2)) + '/' + CAST(DATEPART(yyyy, @Now) AS NVARCHAR(4)) AS DATETIME)
  END

  /* Is this SeasonalFullAddress valid? */
  IF ( @BeginDate <= @Now AND ( @Now < @EndDate OR @EndDate IS NULL ) )
  /* Is valid, make the seasonal change */
  BEGIN
    /* Now, Swap the actual pointer */
    UPDATE AddressToUse
      SET FullAddressKey = @ReplacesFullAddressKey,
          SeasonalRevertFullAddressKey = FullAddressKey
      WHERE FullAddressKey = @FullAddressKey
  END
  ELSE
  /* Not valid, reverse the seasonal change */
  BEGIN
    UPDATE AddressToUse
      SET FullAddressKey = SeasonalRevertFullAddressKey,
          SeasonalRevertFullAddressKey = NULL
      WHERE FullAddressKey = @ReplacesFullAddressKey
        /*
          Only revert if this one is the cause
        */
      AND SeasonalRevertFullAddressKey = @FullAddressKey
        /*
          If this address is in AddressToUse but isn't seasonal,
          we don't want to revert.
        */
      AND SeasonalRevertFullAddressKey IS NOT NULL
  END

  /* Get next row */
  FETCH NEXT
    FROM curSeasonalFullAddress
    INTO  @FullAddressKey,
      @ReplacesFullAddressKey,
        @BeginDate,
      @EndDate,
      @IsSeasonalAnnually
END
/* End iterate: curSeasonalFullAddress */

/* Close and Dealloc: curSeasonalFullAddress */
CLOSE curSeasonalFullAddress
DEALLOCATE curSeasonalFullAddress

GO